﻿-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AnhDQ
-- Create date: 3/16/2011
-- Description:	This stored procedure use to get an Employee record
-- =============================================
ALTER PROCEDURE [SearchMaterial]
(
@MaterialCode VARCHAR(10) = NULL, 
@MaterialName NVARCHAR(50) = NULL, 
@TotalQuantityFrom NUMERIC(10,0) = NULL, 
@TotalQuantityTo NUMERIC(10,0) = NULL,
@IsDelete BIT = 0,
@WarehouseID INT = NULL
)
AS
BEGIN
	DECLARE @SearchMaterialQuery NVARCHAR(MAX) = 
		'
		SELECT 
			m.MaterialID
			, m.MaterialCode
			, m.MaterialName
			, m.Description
			, m.IsDelete
			, whm.WarehouseID
			, whm.Quantity
			, wh.WarehouseName
			, wh.Manager
			, wh.Address
			, wh.OfficePhone
			, wh.Description
			, wh.IsDelete
		FROM 
			[Material] m 
		INNER JOIN
			[WarehouseMaterial] whm
		ON
			m.MaterialID = whm.MaterialID
		INNER JOIN
			[Warehouse] wh
		ON
			whm.WarehouseID = wh.WarehouseID
		WHERE 1=1'
	
	-- Chuoi dieu kien IsDelete 
	DECLARE @IsDeleteConditionTemplate NVARCHAR(MAX) = 
		N'
			AND m.IsDelete = @IsDelete'
	DECLARE @IsDeletePlace NVARCHAR(MAX) = N'@IsDelete'
	DECLARE @IsDeleteCondition NVARCHAR(MAX) = ''
	
	IF (@IsDelete IS NOT NULL)
		SET @IsDeleteCondition = REPLACE ( @IsDeleteConditionTemplate , @IsDeletePlace , @IsDelete )

	-- Chuoi dieu kien MaterialCode 
	DECLARE @MaterialCodeConditionTemplate NVARCHAR(MAX) = 
		N'
			AND m.MaterialCode LIKE ''%@MaterialCode%'' '
	DECLARE @MaterialCodePlace NVARCHAR(MAX) = N'@MaterialCode'
	DECLARE @MaterialCodeCondition NVARCHAR(MAX) = ''

	IF (@MaterialCode IS NOT NULL)
		SET @MaterialCodeCondition = REPLACE ( @MaterialCodeConditionTemplate , @MaterialCodePlace , @MaterialCode )

	-- Chuoi dieu kien MaterialName
	DECLARE @MaterialNameConditionTemplate NVARCHAR(MAX) = 
		N'
			AND m.MaterialName LIKE ''%@MaterialName%'' '
	DECLARE @MaterialNamePlace NVARCHAR(MAX) = N'@MaterialName'
	DECLARE @MaterialNameCondition NVARCHAR(MAX) = ''

	IF (@MaterialName IS NOT NULL)
		SET @MaterialNameCondition = REPLACE ( @MaterialNameConditionTemplate , @MaterialNamePlace , @MaterialName )

	-- Chuoi dieu kien TotalQuantityFrom
	DECLARE @TotalQuantityFromConditionTemplate NVARCHAR(MAX) = 
		N'
			AND whm.Quantity >= @TotalQuantityFrom'
	DECLARE @TotalQuantityFromPlace NVARCHAR(MAX) = N'@TotalQuantityFrom'
	DECLARE @TotalQuantityFromCondition NVARCHAR(MAX) = ''

	IF (@TotalQuantityFrom IS NOT NULL AND @TotalQuantityFrom >= 0 )
		SET @TotalQuantityFromCondition = REPLACE ( @TotalQuantityFromConditionTemplate , @TotalQuantityFromPlace , @TotalQuantityFrom )

	-- Chuoi dieu kien TotalQuantityTo
	DECLARE @TotalQuantityToConditionTemplate NVARCHAR(MAX) = 
		N'
			AND whm.Quantity <= @TotalQuantityTo'
	DECLARE @TotalQuantityToPlace NVARCHAR(MAX) = N'@TotalQuantityTo'
	DECLARE @TotalQuantityToCondition NVARCHAR(MAX) = ''

	IF (@TotalQuantityTo IS NOT NULL AND @TotalQuantityTo >= 0)
		SET @TotalQuantityToCondition = REPLACE ( @TotalQuantityToConditionTemplate , @TotalQuantityToPlace , @TotalQuantityTo )

	-- Chuoi dieu kien WarehouseID
	DECLARE @WarehouseIDConditionTemplate NVARCHAR(MAX) = 
		N'
			AND whm.WarehouseID = @WarehouseID'
	DECLARE @WarehouseIDPlace NVARCHAR(MAX) = N'@WarehouseID'
	DECLARE @WarehouseIDCondition NVARCHAR(MAX) = ''

	IF (@WarehouseID IS NOT NULL)
		SET @WarehouseIDCondition = REPLACE ( @WarehouseIDConditionTemplate , @WarehouseIDPlace , @WarehouseID )

	-- Thuc hien query

	SELECT @SearchMaterialQuery += @IsDeleteCondition + @WarehouseIDCondition + @MaterialCodeCondition + @MaterialNameCondition
									+ @TotalQuantityFromCondition + @TotalQuantityToCondition

	PRINT @SearchMaterialQuery

	EXEC sp_sqlexec @p1 = @SearchMaterialQuery
		
END

/*
USE WarehouseManagement
GO

EXEC [SearchMaterial] @TotalQuantityFrom = 10 ,@TotalQuantityTo = 0
*/